SQL Server中授予用户查看对象定义的权限 |
您所在的位置:网站首页 › sql server授予用户权限 › SQL Server中授予用户查看对象定义的权限 |
在SQL Server中,有时候需要给一些登录名(用户)授予查看所有或部分对象(存储过程、函数、视图、表)的定义权限存。如果是部分存储过程、函数、视图授予查看定义的权限,那么就像下面脚本所示,比较繁琐:
GRANT VIEW DEFINITION ON YOUR_PROCEDURE TO USERNAME;
GRANT VIEW DEFINITION ON YOUR_FUNCTION TO USERNAME;
GRANT VIEW DEFINITION ON YOUR_VIEW TO USERANEM;
.....................................................
如果是批量授权,那么可以使用下面脚本生成授权脚本。然后执行生成的脚本:
USE DatabaseName; GO ---给用户授予查看存储过程定义的权限 DECLARE @loginname VARCHAR(32); SET @loginname='[eopms_reader]' SELECT 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.' + QUOTENAME(name) + ' TO ' + @loginname + ';' FROM sys.procedures; --给用户授予查看自定义函数定义的权限 SELECT 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.' + QUOTENAME(name) + ' TO ' + @loginname + ';' FROM sys.objects WHERE type_desc IN ( 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION', 'AGGREGATE_FUNCTION' ); --给用户授予查看视图定义的权限 SELECT 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.' + QUOTENAME(name) + ' TO ' + @loginname + ';' FROM sys.views; --给用户授予查看视表定义的权限 SELECT 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + QUOTENAME(name) + ' TO ' + @loginname + ';' FROM sys.tables;
如果你想直接执行脚本,不想生成授权脚本,那么可以使用下面脚本实现授权。当然前提是你选择所要授权的数据库(USE DatabaseName) DECLARE @loginname VARCHAR(32); DECLARE @sqlcmd NVARCHAR(MAX); DECLARE @name sysname; DECLARE @schema_id INT; SET @loginname='[kerry]' DECLARE procedure_cursor CURSOR FORWARD_ONLY FOR SELECT schema_id, name FROM sys.procedures; OPEN procedure_cursor; FETCH NEXT FROM procedure_cursor INTO @schema_id, @name; ---给用户授予查看存储过程定义的权限 WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlcmd= 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(@schema_id) + '.' + QUOTENAME(@name) + ' TO ' + @loginname + ';' --PRINT @sqlcmd; EXEC sp_executesql @sqlcmd; FETCH NEXT FROM procedure_cursor INTO @schema_id, @name; END CLOSE procedure_cursor; DEALLOCATE procedure_cursor; DECLARE function_cursor CURSOR FAST_FORWARD FOR SELECT schema_id, name FROM sys.objects WHERE type_desc IN ( 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION', 'AGGREGATE_FUNCTION' ); --给用户授予查看自定义函数定义的权限 OPEN function_cursor; FETCH NEXT FROM function_cursor INTO @schema_id,@name; WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlcmd= 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(@schema_id) + '.' + QUOTENAME(@name) + ' TO ' + @loginname + ';' --PRINT @sqlcmd; EXEC sp_executesql @sqlcmd; FETCH NEXT FROM function_cursor INTO @schema_id, @name; END CLOSE function_cursor; DEALLOCATE function_cursor; DECLARE view_cursor CURSOR FAST_FORWARD FOR SELECT schema_id, name FROM sys.views; OPEN view_cursor; FETCH NEXT FROM view_cursor INTO @schema_id, @name; WHILE @@FETCH_STATUS = 0 BEGIN --给用户授予查看视图定义的权限 SET @sqlcmd= 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(@schema_id) + '.' + QUOTENAME(@name) + ' TO ' + @loginname + ';' --PRINT @sqlcmd; EXEC sys.sp_executesql @sqlcmd; FETCH NEXT FROM view_cursor INTO @schema_id, @name; END CLOSE view_cursor; DEALLOCATE view_cursor; DECLARE table_cursor CURSOR FAST_FORWARD FOR SELECT schema_id,name FROM sys.tables; OPEN table_cursor; FETCH NEXT FROM table_cursor INTO @schema_id, @name; WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlcmd ='GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(@schema_id) + '.' + QUOTENAME(@name) + ' TO ' + @loginname + ';' --PRINT @sqlcmd; EXEC sys.sp_executesql @sqlcmd; FETCH NEXT FROM table_cursor INTO @schema_id, @name; END CLOSE table_cursor; DEALLOCATE table_cursor;
如果单个用户授权,那么使用上面方法已经OK,但是一个系统,经常遇到这种授权情况,那么使用上面方法也会变得非常繁琐;另外,当新增表、视图、存储过程后,又需要修改相关授权脚本,这样变得非常麻烦繁琐,那么可以创建一个数据库角色,授予查看数据库对象定义的权限,然后新建登录名时,将这个数据库角色授予新建的登录名即可。这样以后即使有权限啥变化,只需要调整数据库角色的权限即可,不需修改登录名。对于重复性的授权工作会变得非常方便、简单。例如,需要给新建的登录名kerry授予查看数据库对象定义的权限,那么首先创建数据库角色View_Definiton,然后将查看定义的权限授予数据库角色View_Definiton,最后,创建了登录名kerry后,只需将角色View_Definiton授予kerry即可。
USE AdventureWorks2014; GO CREATE ROLE [View_Definiton] AUTHORIZATION [dbo] GO DECLARE @loginname VARCHAR(32); SET @loginname='View_Definiton' SELECT 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.' + QUOTENAME(name) + ' TO ' + @loginname + ';' FROM sys.procedures; --给用户授予查看自定义函数定义的权限 SELECT 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.' + QUOTENAME(name) + ' TO ' + @loginname + ';' FROM sys.objects WHERE type_desc IN ( 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION', 'AGGREGATE_FUNCTION' ); --给用户授予查看视图定义的权限 SELECT 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.' + QUOTENAME(name) + ' TO ' + @loginname + ';' FROM sys.views; --给用户授予查看视表定义的权限 SELECT 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) +'.' + QUOTENAME(name) + ' TO ' + @loginname + ';' FROM sys.tables;
ALTER ROLE [View_Definiton] ADD MEMBER [kerry] GO |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |